package com.example.backend.repository;

import com.example.backend.domain.entity.TicketStatusTracking;
import com.example.backend.domain.enums.TicketStatusTrackingType;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.Instant;
import java.util.List;

/**
 * 工单状态跟踪Repository
 */
@Repository
public interface TicketStatusTrackingRepository extends JpaRepository<TicketStatusTracking, Long> {
    
    /**
     * 根据工单ID获取状态跟踪记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findByTicketIdOrderByCreatedAtDesc(@Param("ticketId") Long ticketId);
    
    /**
     * 根据工单ID分页获取状态跟踪记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId ORDER BY t.createdAt DESC")
    Page<TicketStatusTracking> findByTicketIdOrderByCreatedAtDesc(@Param("ticketId") Long ticketId, Pageable pageable);
    
    /**
     * 根据工单ID和跟踪类型获取记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType = :trackingType ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findByTicketIdAndTrackingTypeOrderByCreatedAtDesc(@Param("ticketId") Long ticketId, @Param("trackingType") TicketStatusTrackingType trackingType);
    
    /**
     * 根据操作人ID获取操作记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.operatorId = :operatorId ORDER BY t.createdAt DESC")
    Page<TicketStatusTracking> findByOperatorIdOrderByCreatedAtDesc(@Param("operatorId") Long operatorId, Pageable pageable);
    
    /**
     * 根据时间范围获取跟踪记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.createdAt BETWEEN :startTime AND :endTime ORDER BY t.createdAt DESC")
    Page<TicketStatusTracking> findByCreatedAtBetweenOrderByCreatedAtDesc(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime, Pageable pageable);
    
    /**
     * 获取工单的状态变更历史
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType = 'STATUS_CHANGED' ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findStatusChangeHistoryByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取工单的分配历史
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType IN ('ASSIGNED', 'REASSIGNED') ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findAssignmentHistoryByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取工单的优先级变更历史
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType = 'PRIORITY_CHANGED' ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findPriorityChangeHistoryByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取用户可见的跟踪记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.isVisibleToCustomer = true ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findVisibleToCustomerByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取系统操作的跟踪记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.isSystemOperation = true ORDER BY t.createdAt DESC")
    List<TicketStatusTracking> findSystemOperationsByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 统计工单的操作次数
     */
    @Query("SELECT COUNT(t) FROM TicketStatusTracking t WHERE t.ticketId = :ticketId")
    Long countByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 统计特定类型的操作次数
     */
    @Query("SELECT COUNT(t) FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType = :trackingType")
    Long countByTicketIdAndTrackingType(@Param("ticketId") Long ticketId, @Param("trackingType") TicketStatusTrackingType trackingType);
    
    /**
     * 获取工单的最后一次操作
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId ORDER BY t.createdAt DESC LIMIT 1")
    TicketStatusTracking findLastOperationByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取工单的最后一次状态变更
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType = 'STATUS_CHANGED' ORDER BY t.createdAt DESC LIMIT 1")
    TicketStatusTracking findLastStatusChangeByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取工单的最后一次分配
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId = :ticketId AND t.trackingType IN ('ASSIGNED', 'REASSIGNED') ORDER BY t.createdAt DESC LIMIT 1")
    TicketStatusTracking findLastAssignmentByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 根据多个工单ID获取跟踪记录
     */
    @Query("SELECT t FROM TicketStatusTracking t WHERE t.ticketId IN :ticketIds ORDER BY t.ticketId, t.createdAt DESC")
    List<TicketStatusTracking> findByTicketIdInOrderByTicketIdAndCreatedAtDesc(@Param("ticketIds") List<Long> ticketIds);
    
    /**
     * 获取操作人的操作统计
     */
    @Query("SELECT t.trackingType, COUNT(t) FROM TicketStatusTracking t WHERE t.operatorId = :operatorId GROUP BY t.trackingType")
    List<Object[]> getOperationStatsByOperatorId(@Param("operatorId") Long operatorId);
    
    /**
     * 获取工单的操作统计
     */
    @Query("SELECT t.trackingType, COUNT(t) FROM TicketStatusTracking t WHERE t.ticketId = :ticketId GROUP BY t.trackingType")
    List<Object[]> getOperationStatsByTicketId(@Param("ticketId") Long ticketId);
    
    /**
     * 获取时间范围内的操作统计
     */
    @Query("SELECT t.trackingType, COUNT(t) FROM TicketStatusTracking t WHERE t.createdAt BETWEEN :startTime AND :endTime GROUP BY t.trackingType")
    List<Object[]> getOperationStatsByTimeRange(@Param("startTime") Instant startTime, @Param("endTime") Instant endTime);
    
    /**
     * 删除指定工单的所有跟踪记录
     */
    void deleteByTicketId(Long ticketId);
    
    /**
     * 删除指定时间之前的跟踪记录
     */
    @Query("DELETE FROM TicketStatusTracking t WHERE t.createdAt < :cutoffTime")
    int deleteByCreatedAtBefore(@Param("cutoffTime") Instant cutoffTime);
}

